{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "### 查看每列缺失值的数量和比率函数"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def missing(combine):\n",
    "    mark=0\n",
    "    for data in combine:\n",
    "        dic={}\n",
    "        dic['Alength']=len(data)\n",
    "        for i in data.isnull().any()[data.isnull().any()].index:\n",
    "            dic[i]=[str(len(data[data[i].isnull()]))+'   '+str(round(100*len(data[data[i].isnull()])/len(data),3))+'%']\n",
    "        if mark==100:\n",
    "            nantest=pd.DataFrame(dic)\n",
    "            break\n",
    "        else:\n",
    "            nantrain=pd.DataFrame(dic)\n",
    "        mark=100\n",
    "    return nantrain,nantest"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 删除instance_id重复的列，组合train和test到一起进行预处理，一天下午4点到第二天下午4点为新的一天"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "predict_category_property列缺失的行数： 4\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "#['predict_category_property', 'item_category_list', 'item_property_list']\n",
    "train=pd.read_csv(r'D:\\Data\\IJCAI\\train.csv',na_values=-1)\n",
    "test=pd.read_csv(r'D:\\Data\\IJCAI\\test.csv',na_values=-1)\n",
    "train.drop_duplicates(inplace=True)\n",
    "train=train.sort_values(by=['instance_id'])\n",
    "train.index=np.arange(len(train))\n",
    "for ind in train.index:                     #删除完全重复的记录并对只有instance_id重复的记录重新赋值\n",
    "    if ind<(len(train)-1):\n",
    "        if train.loc[ind,'instance_id']==train.loc[(ind+1),'instance_id']:\n",
    "            train.loc[(ind+1),'instance_id']=train.loc[ind,'instance_id']+1\n",
    "print('predict_category_property列缺失的行数：',len(train[train['predict_category_property'].isnull()]))\n",
    "train.drop(list(train[train['predict_category_property'].isnull()].index),axis=0,inplace=True)\n",
    "\n",
    "train['timestamp']=pd.to_datetime(train['context_timestamp'].copy(),unit='s')\n",
    "train['day']=train['timestamp'].dt.day\n",
    "test['timestamp']=pd.to_datetime(test['context_timestamp'].copy(),unit='s')\n",
    "test['day']=test['timestamp'].dt.day\n",
    " \n",
    "is_trade=train['is_trade']                                  #将train和test组合到一起进行处理\n",
    "train.drop(['is_trade'],axis=1,inplace=True)\n",
    "train['is_trade']=is_trade\n",
    "test['is_trade']=1000\n",
    "train=pd.concat([train,test],axis=0)\n",
    "train.index=np.arange(len(train))\n",
    "\n",
    "count=17\n",
    "train['newday']=0\n",
    "for day in [17,18,19,20,21,22,23,24]:                                        #一天下午4点到第二天下午4点\n",
    "    lis=(train[(train['timestamp']>='2018-09-%s 16:00:00'%(day))&(train['timestamp']<'2018-09-%s 16:00:00'%(day+1))].index)\n",
    "    train.loc[lis,'newday']=count\n",
    "    count+=1"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## “前一天”商品的转换率和“前两天”的转换率"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "train is done！\n"
     ]
    }
   ],
   "source": [
    "train['num_conversion']=0\n",
    "train['num_conversiontd']=0\n",
    "for newday in set(train['newday'].values):\n",
    "    if newday<18:\n",
    "        continue\n",
    "    else:\n",
    "        for u in set(train[train['newday']==newday]['item_id'].values):\n",
    "            if u in train[train['newday']==(newday-1)]['item_id'].values:\n",
    "                train.loc[train[(train['item_id']==u)&(train['newday']==newday)].index,'num_conversion']=\\\n",
    "                len(train[(train['item_id']==u)&(train['newday']==(newday-1))&(train['is_trade']==1)])/len(train[(train['item_id']==u)&(train['newday']==(newday-1))])\n",
    "            if newday<19:\n",
    "                continue\n",
    "            else:\n",
    "                if u in train[(train['newday']==(newday-1))|(train['newday']==(newday-2))]['item_id'].values:\n",
    "                    train.loc[train[(train['item_id']==u)&(train['newday']==newday)].index,'num_conversiontd']=\\\n",
    "                    (len(train[(train['item_id']==u)&(train['newday']==(newday-1))&(train['is_trade']==1)])+\\\n",
    "                     len(train[(train['item_id']==u)&(train['newday']==(newday-2))&(train['is_trade']==1)]))/(len(train[(train['item_id']==u)&(train['newday']==(newday-1))])+\\\n",
    "                                                                                                    len(train[(train['item_id']==u)&(train['newday']==(newday-2))]))\n",
    "print('train has done！')\n",
    "train.to_csv(r'D:\\Data\\IJCAI\\no_fixedday\\train_conversion.csv',index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 提取\"每天\"用户点击数，商品被点击数，各个时间间隔,并将train和test重新分开，test按照最开始的顺序排序"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Train has being Done!\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\ProgramData\\Anaconda3\\lib\\site-packages\\ipykernel_launcher.py:67: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "length of test is： 18371\n",
      "length of new is： 18371\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "train=pd.read_csv(r'D:\\Data\\IJCAI\\no_fixedday\\train_conversion.csv',na_values=-1)\n",
    "train=train.sort_values(by=['newday','user_id'])                               #同一天同一个用户的点击广告的次数——--用户的搜索次数和点击次数\n",
    "h=train.groupby(by=['newday','user_id']).count()\n",
    "lis=[]\n",
    "for i,j in h.index:\n",
    "    lis.extend([h.loc[i].loc[j]['instance_id']]*h.loc[i].loc[j]['instance_id'])\n",
    "train['num_click']=lis\n",
    "\n",
    "train=train.sort_values(by=['newday','item_id'])                              #同一天同一件商品被搜索点击次数-----商品的被搜索次数和受欢迎程度\n",
    "h=train.groupby(by=['newday','item_id']).count()\n",
    "lis=[]\n",
    "for i,j in h.index:\n",
    "    lis.extend([h.loc[i].loc[j]['instance_id']]*h.loc[i].loc[j]['instance_id'])\n",
    "train['num_show']=lis\n",
    "    \n",
    "train=train.sort_values(by=['item_id','context_timestamp'])                 #同一件商品被搜索点击的时间间隔\n",
    "train.index=np.arange(len(train))\n",
    "lis=[]\n",
    "for val in set(train['item_id'].values):\n",
    "    lis.append(train[train['item_id']==val].index[-1])\n",
    "temp=train['context_timestamp'].diff()\n",
    "for i in lis:\n",
    "    temp.loc[i+1]=0\n",
    "temp.loc[0]=0\n",
    "train['item_time_diff']=temp\n",
    "\n",
    "train=train.sort_values(by=['newday','item_id','context_timestamp'])          #同一天同一件商品的被搜索点击的时间间隔\n",
    "train.index=np.arange(len(train))\n",
    "lis=[]\n",
    "for d in [17,18,19,20,21,22,23,24]:\n",
    "    for val in set(train[train['newday']==d]['item_id'].values):\n",
    "        lis.append(train[(train['item_id']==val)&(train['newday']==d)].index[-1])\n",
    "temp=train['context_timestamp'].diff()\n",
    "for i in lis:\n",
    "    temp.loc[i+1]=0\n",
    "temp.loc[0]=0\n",
    "train['newday_item_diff']=temp\n",
    "    \n",
    "train=train.sort_values(by=['newday','user_id','context_timestamp'])          #同一天相同用户的不同商品点击时间间隔\n",
    "train.index=np.arange(len(train))\n",
    "lis=[]\n",
    "for d in [17,18,19,20,21,22,23,24]:\n",
    "    for val in set(train[train['newday']==d]['user_id'].values):\n",
    "        lis.append(train[(train['user_id']==val)&(train['newday']==d)].index[-1])\n",
    "temp=train['context_timestamp'].diff()\n",
    "for i in lis:\n",
    "    temp.loc[i+1]=0\n",
    "temp.loc[0]=0\n",
    "train['newday_user_diff']=temp\n",
    "\n",
    "train.index=np.arange(len(train))\n",
    "train['num_item_newday']=0\n",
    "for val in set(train['item_id'].values):               #商品被推荐并被点击的天数\n",
    "    train.loc[list(train[train['item_id']==val].index),'num_item_newday']=len(set(train[train['item_id']==val]['newday'].values))\n",
    "    \n",
    "train['num_user_newday']=0\n",
    "for val in set(train['user_id'].values):               #用户点击广告的天数\n",
    "    train.loc[list(train[train['user_id']==val].index),'num_user_newday']=len(set(train[train['user_id']==val]['newday'].values))\n",
    "##########################################################################################################################################\n",
    "test=train[train['is_trade']==1000]\n",
    "train.drop(list(train[train['is_trade']==1000].index),axis=0,inplace=True)\n",
    "train.to_csv(r'D:\\Data\\IJCAI\\no_fixedday\\train_pre1.csv',index=False)\n",
    "print('Train has being Done!')\n",
    "\n",
    "test.drop(['is_trade'],axis=1,inplace=True)                  #test按照最初的顺序排序好提交结果\n",
    "test.index=np.arange(len(test))\n",
    "print('length of test is：',len(test))\n",
    "new=pd.read_csv(r'D:\\Data\\IJCAI\\test.csv')\n",
    "print('length of new is：',len(new))\n",
    "# print('Is set(new[instance_id].values)==set(test[instance_id].values)',set(new['instance_id'].values)==set(test['instance_id'].values))\n",
    "lis=[]\n",
    "for i in new.index:\n",
    "    lis.extend(list(test[test['instance_id']==new.loc[i,'instance_id']].index))\n",
    "test=test.loc[lis]\n",
    "test.index=np.arange(len(test))\n",
    "test.to_csv(r'D:\\Data\\IJCAI\\no_fixedday\\test_pre1.csv',index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 对缺失值进行填充"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Alength</th>\n",
       "      <th>item_brand_id</th>\n",
       "      <th>item_city_id</th>\n",
       "      <th>item_sales_level</th>\n",
       "      <th>shop_review_positive_rate</th>\n",
       "      <th>shop_score_delivery</th>\n",
       "      <th>shop_score_description</th>\n",
       "      <th>shop_score_service</th>\n",
       "      <th>user_age_level</th>\n",
       "      <th>user_gender_id</th>\n",
       "      <th>user_occupation_id</th>\n",
       "      <th>user_star_level</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>478107</td>\n",
       "      <td>473   0.099%</td>\n",
       "      <td>277   0.058%</td>\n",
       "      <td>913   0.191%</td>\n",
       "      <td>7   0.001%</td>\n",
       "      <td>59   0.012%</td>\n",
       "      <td>59   0.012%</td>\n",
       "      <td>59   0.012%</td>\n",
       "      <td>964   0.202%</td>\n",
       "      <td>12902   2.699%</td>\n",
       "      <td>964   0.202%</td>\n",
       "      <td>964   0.202%</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Alength item_brand_id  item_city_id item_sales_level  \\\n",
       "0   478107  473   0.099%  277   0.058%     913   0.191%   \n",
       "\n",
       "  shop_review_positive_rate shop_score_delivery shop_score_description  \\\n",
       "0                7   0.001%         59   0.012%            59   0.012%   \n",
       "\n",
       "  shop_score_service user_age_level  user_gender_id user_occupation_id  \\\n",
       "0        59   0.012%   964   0.202%  12902   2.699%       964   0.202%   \n",
       "\n",
       "  user_star_level  \n",
       "0    964   0.202%  "
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "train=pd.read_csv(r'D:\\Data\\IJCAI\\no_fixedday\\train_pre1.csv',na_values=-1)\n",
    "test=pd.read_csv(r'D:\\Data\\IJCAI\\no_fixedday\\test_pre1.csv',na_values=-1)\n",
    "combine=[train,test]\n",
    "nantrain,nantest=missing(combine)\n",
    "nantrain"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Alength</th>\n",
       "      <th>item_brand_id</th>\n",
       "      <th>item_city_id</th>\n",
       "      <th>item_sales_level</th>\n",
       "      <th>shop_score_delivery</th>\n",
       "      <th>shop_score_description</th>\n",
       "      <th>shop_score_service</th>\n",
       "      <th>user_age_level</th>\n",
       "      <th>user_gender_id</th>\n",
       "      <th>user_occupation_id</th>\n",
       "      <th>user_star_level</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>18371</td>\n",
       "      <td>18   0.098%</td>\n",
       "      <td>6   0.033%</td>\n",
       "      <td>35   0.191%</td>\n",
       "      <td>1   0.005%</td>\n",
       "      <td>1   0.005%</td>\n",
       "      <td>1   0.005%</td>\n",
       "      <td>18   0.098%</td>\n",
       "      <td>469   2.553%</td>\n",
       "      <td>18   0.098%</td>\n",
       "      <td>18   0.098%</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Alength item_brand_id item_city_id item_sales_level shop_score_delivery  \\\n",
       "0    18371   18   0.098%   6   0.033%      35   0.191%          1   0.005%   \n",
       "\n",
       "  shop_score_description shop_score_service user_age_level user_gender_id  \\\n",
       "0             1   0.005%         1   0.005%    18   0.098%   469   2.553%   \n",
       "\n",
       "  user_occupation_id user_star_level  \n",
       "0        18   0.098%     18   0.098%  "
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "nantest"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1000 2000 3000 4000 5000 6000 7000 8000 9000 10000 11000 12000 Done!\n"
     ]
    }
   ],
   "source": [
    "# corr=train[['instance_id', 'item_id', 'item_brand_id', 'item_city_id', 'item_price_level', 'item_sales_level', 'item_collected_level', \\\n",
    "#             'item_pv_level', 'user_id', 'user_gender_id', 'user_age_level', 'user_occupation_id','user_star_level', 'context_id', \\\n",
    "#             'context_timestamp', 'context_page_id','shop_id', 'shop_review_num_level', 'shop_review_positive_rate', 'shop_star_level', \\\n",
    "#             'shop_score_service', 'shop_score_delivery', 'shop_score_description', 'timestamp', 'day', 'is_trade', 'num_conversion', \\\n",
    "#             'num_conversiontd', 'num_click', 'num_show', 'item_time_diff', 'day_item_diff', 'day_user_diff']].corr()\n",
    "# corr[corr['shop_review_positive_rate']>0.1]['shop_review_positive_rate'].sort_values(ascending=False).head(6)#shop_score_description\n",
    "train.index=np.arange(len(train))\n",
    "test.index=np.arange(len(test))\n",
    "for ind in train[train['shop_review_positive_rate'].isnull()].index:\n",
    "    item_price_level=train.loc[ind,'item_price_level']\n",
    "    train.loc[ind,'shop_review_positive_rate']=train[train['item_price_level']==item_price_level]['shop_review_positive_rate'].median()\n",
    "# corr[corr['shop_score_delivery']>0.1]['shop_score_delivery'].sort_values(ascending=False).head(6)\n",
    "#['shop_score_delivery','shop_score_description','shop_score_service']这四列是同时缺失的需要一起填充\n",
    "#train[train['user_age_level'].isnull()][['is_trade','user_age_level','user_occupation_id','user_star_level']]\n",
    "h=['shop_score_delivery','shop_score_description','shop_score_service']\n",
    "combine=[train,test]\n",
    "for data in combine:\n",
    "    for hj in h:\n",
    "        for ind in data[data[hj].isnull()].index:\n",
    "            shop_review_positive_rate=data.loc[ind,'shop_review_positive_rate']\n",
    "            item_price_level=data.loc[ind,'item_price_level']\n",
    "            temp=data[(data['shop_review_positive_rate']==shop_review_positive_rate)&(data['item_price_level']==item_price_level)]\n",
    "            if temp['shop_score_service'].isnull().any():\n",
    "                data.loc[ind,'shop_score_service']=data[data['item_price_level']==item_price_level]['shop_score_service'].median()\n",
    "            else:\n",
    "                data.loc[ind,'shop_score_service']=temp['shop_score_service'].median()\n",
    "            if temp['shop_score_delivery'].isnull().any():\n",
    "                data.loc[ind,'shop_score_delivery']=data[data['item_price_level']==item_price_level]['shop_score_delivery'].median()\n",
    "            else:\n",
    "                data.loc[ind,'shop_score_delivery']=round(temp[~temp['shop_score_delivery'].isnull()]['shop_score_delivery'].median(),6)\n",
    "            if temp['shop_score_description'].isnull().any():\n",
    "                data.loc[ind,'shop_score_description']=data[data['item_price_level']==item_price_level]['shop_score_description'].median()\n",
    "            else:\n",
    "                data.loc[ind,'shop_score_description']=temp[~temp['shop_score_description'].isnull()]['shop_score_description'].median()\n",
    "# corr[corr['user_age_level']>0.1]['user_age_level'].sort_values(ascending=False).head(6)\n",
    "#['is_trade','user_age_level','user_occupation_id','user_star_level']这三列是同时缺失的需要一起填充\n",
    "combine=[train,test]\n",
    "h=['user_age_level','user_occupation_id','user_star_level']\n",
    "for data in combine:\n",
    "    for hj in h:\n",
    "        for ind in data[data[hj].isnull()].index:\n",
    "            shop_score_description=data.loc[ind,'shop_score_description']\n",
    "            item_price_level=data.loc[ind,'item_price_level']\n",
    "            temp=data[(data['shop_score_description']==shop_score_description)&(data['item_price_level']==item_price_level)]\n",
    "            if temp['user_age_level'].isnull().any():\n",
    "                data.loc[ind,'user_age_level']=data[data['item_price_level']==item_price_level]['user_age_level'].median()\n",
    "            else:\n",
    "                data.loc[ind,'user_age_level']=temp['user_age_level'].median()\n",
    "            if temp['user_occupation_id'].isnull().any():\n",
    "                if (data[data['shop_score_description']==shop_score_description]['user_occupation_id'].isnull().any())\\\n",
    "                and (data[data['item_price_level']==item_price_level]['user_occupation_id'].isnull().any()):\n",
    "                    data.loc[ind,'user_occupation_id']=data.loc[ind+6,'user_occupation_id']\n",
    "                elif (data[data['shop_score_description']==shop_score_description]['user_occupation_id'].isnull().any())\\\n",
    "                and (not data[data['item_price_level']==item_price_level]['user_occupation_id'].isnull().any()):\n",
    "                    data.loc[ind,'user_occupation_id']=data[data['item_price_level']==item_price_level]['user_occupation_id'].value_counts().index[0]\n",
    "                else:\n",
    "                    data.loc[ind,'user_occupation_id']=data[data['shop_score_description']==shop_score_description]['user_occupation_id'].value_counts().index[0]\n",
    "            else:\n",
    "                data.loc[ind,'user_occupation_id']=temp['user_occupation_id'].value_counts().index[0]\n",
    "            if temp['user_star_level'].isnull().any():\n",
    "                data.loc[ind,'user_star_level']=data[data['shop_score_description']==shop_score_description]['user_star_level'].median()\n",
    "            else:\n",
    "                data.loc[ind,'user_star_level']=temp['user_star_level'].median()\n",
    "    for ind in data[data['user_star_level'].isnull()].index:\n",
    "        data.loc[ind,'user_star_level']=data.loc[ind-6,'user_star_level']\n",
    "    for ind in data[data['user_age_level'].isnull()].index:\n",
    "        data.loc[ind,'user_age_level']=data.loc[ind-6,'user_age_level']\n",
    "# corr[corr['item_brand_id']>0.06]['item_brand_id'].sort_values(ascending=False).head(6)\n",
    "combine=[train,test]\n",
    "for data in combine:\n",
    "    for ind in data[data['item_brand_id'].isnull()].index:\n",
    "        shop_id=data.loc[ind,'shop_id']\n",
    "        temp=data[data['shop_id']==shop_id]\n",
    "        if len(temp['item_brand_id'].isnull())==len(temp):\n",
    "            data.loc[ind,'item_brand_id']=data.loc[ind-1,'item_brand_id']\n",
    "        else:\n",
    "            data.loc[ind,'item_brand_id']=temp['item_brand_id'].value_counts().index[0]\n",
    "# corr[corr['item_city_id']>0.006]['item_city_id'].sort_values(ascending=False).head(6)\n",
    "combine=[train,test]\n",
    "for data in combine:\n",
    "    for ind in data[data['item_city_id'].isnull()].index:\n",
    "        item_price_level=data.loc[ind,'item_price_level']\n",
    "        temp=data[data['item_price_level']==item_price_level]\n",
    "        data.loc[ind,'item_city_id']=temp['item_city_id'].value_counts().index[0]\n",
    "# corr[corr['item_sales_level']>0.1]['item_sales_level'].sort_values(ascending=False).head(6)\n",
    "combine=[train,test]\n",
    "for data in combine:\n",
    "    for ind in data[data['item_sales_level'].isnull()].index:\n",
    "        item_collected_level=data.loc[ind,'item_collected_level']\n",
    "        item_pv_level=data.loc[ind,'item_pv_level']\n",
    "        temp=data[(data['item_collected_level']==item_collected_level)&(data['item_pv_level']==item_pv_level)]\n",
    "        if temp['item_sales_level'].isnull().any():\n",
    "            data.loc[ind,'item_sales_level']=data[data['item_collected_level']==item_collected_level]['item_sales_level'].median()\n",
    "        else:\n",
    "            data.loc[ind,'item_sales_level']=temp['item_sales_level'].median()\n",
    "# corr[corr['user_gender_id']>0.01]['user_gender_id'].sort_values(ascending=False).head(6)\n",
    "combine=[train,test]\n",
    "for data in combine:\n",
    "    count=0\n",
    "    for ind in data[data['user_gender_id'].isnull()].index:\n",
    "        count+=1\n",
    "        user_age_level=data.loc[ind,'user_age_level']\n",
    "        temp=data[data['user_age_level']==user_age_level]\n",
    "        if (len(temp)==0) and (len(data[data['user_age_level']==user_age_level]['user_gender_id'])!=0):\n",
    "            data.loc[ind,'user_gender_id']=data[data['user_age_level']==user_age_level]['user_gender_id'].value_counts().index[0]\n",
    "        elif temp['user_gender_id'].isnull().any():\n",
    "            data.loc[ind,'user_gender_id']=0\n",
    "        else:\n",
    "            data.loc[ind,'user_gender_id']=temp['user_gender_id'].value_counts().index[0]\n",
    "        if count%1000==0:\n",
    "            print(count,end=' ')\n",
    "train.to_csv(r'D:\\Data\\IJCAI\\no_fixedday\\train_misspre.csv',index=False)\n",
    "test.to_csv(r'D:\\Data\\IJCAI\\no_fixedday\\test_misspre.csv',index=False)\n",
    "print('Done!')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 处理时间过长将之前处理好的广告商品和预测属性和类目数量和比率复制过来"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "tra=pd.read_csv(r'D:\\Data\\IJCAI\\no_fixedday\\train_misspre.csv')\n",
    "tes=pd.read_csv(r'D:\\Data\\IJCAI\\no_fixedday\\test_misspre.csv')\n",
    "train=pd.read_csv(r'D:\\Data\\IJCAI\\no_fixed\\train_mispret.csv')\n",
    "test=pd.read_csv(r'D:\\Data\\IJCAI\\no_fixed\\test_mispret.csv')\n",
    "print(len(tra),len(train),len(set(tra['instance_id'].values)),len(set(train['instance_id'].values)))\n",
    "tra=tra.sort_values(by=['instance_id'])\n",
    "train=train.sort_values(by=['instance_id'])\n",
    "tra.index=np.arange(len(tra))\n",
    "train.index=np.arange(len(train))\n",
    "for col in ['com_pre_cate', 'com_pre_pro', 'com_pre_cateprob', 'com_pre_proprob']:\n",
    "    tra[col]=train[col]\n",
    "    tes[col]=test[col]\n",
    "tra.to_csv(r'D:\\Data\\IJCAI\\no_fixedday\\train_mispret.csv',index=False)\n",
    "tes.to_csv(r'D:\\Data\\IJCAI\\no_fixedday\\test_mispret.csv',index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 广告商品和预测属性和类目数量和比率"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "10000 20000 30000 40000 50000 60000 70000 80000 90000 100000 110000 120000 130000 140000 150000 160000 170000 180000 190000 200000 210000 220000 230000 240000 250000 260000 270000 280000 290000 300000 310000 320000 330000 340000 350000 360000 370000 380000 390000 400000 410000 420000 430000 440000 450000 460000 470000 10000 50647.33700275421\n"
     ]
    }
   ],
   "source": [
    "# import time\n",
    "# import math\n",
    "# import pandas as pd\n",
    "# import numpy as np\n",
    "# train=pd.read_csv(r'D:\\Data\\IJCAI\\no_fixedday\\train_misspre.csv')\n",
    "# test=pd.read_csv(r'D:\\Data\\IJCAI\\no_fixedday\\test_misspre.csv')\n",
    "# s=time.time()\n",
    "# combine=[train,test]\n",
    "# for data in combine:\n",
    "#     data['com_pre_cate']=0\n",
    "#     data['com_pre_pro']=0\n",
    "#     data['com_pre_cateprob']=0\n",
    "#     data['com_pre_proprob']=0\n",
    "#     data['num_true_property']=0\n",
    "#     data['num_pred_category']=0\n",
    "#     data['num_pred_property']=0\n",
    "#     for ind in data.index:\n",
    "#         Category=data.loc[ind,'item_category_list'].split(';')\n",
    "#         Property=data.loc[ind,'item_property_list'].split(';')\n",
    "#         liscate=[]\n",
    "#         lispro=[]\n",
    "#         for i in data.loc[ind,'predict_category_property'].split(';'):\n",
    "#             liscate.append(i.split(':')[0])\n",
    "#             lispro.extend(i.split(':')[1].split(','))\n",
    "#         while '1000' in Category:\n",
    "#             Category.remove('1000')\n",
    "#         while '-1' in lispro:\n",
    "#             lispro.remove('-1')\n",
    "#         comcate=len(set(Category)&set(liscate))\n",
    "#         compro=len(set(Property)&set(lispro))\n",
    "#         data.loc[ind,['com_pre_cate','com_pre_pro','com_pre_cateprob','com_pre_proprob','num_true_property',\\\n",
    "#                       'num_pred_category','num_pred_property']]=[comcate,compro,comcate/math.sqrt(len(Category)*len(liscate)+1),\\\n",
    "#                                                                  compro/math.sqrt(len(Property)*len(lispro)+1),len(Property),\\\n",
    "#                                                                  len(liscate),len(lispro)]\n",
    "#         if (ind %10000==0) and (ind!=0):\n",
    "#             print(ind,end=' ')\n",
    "# e=time.time()\n",
    "# print(e-s)\n",
    "# train.to_csv(r'D:\\Data\\IJCAI\\no_fixedday\\train_mispret.csv',index=False)\n",
    "# test.to_csv(r'D:\\Data\\IJCAI\\no_fixedday\\test_mispret.csv',index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 将'item_category_list'列分成三列，并去掉列值相同的'item_category_list_dup'列"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "10000 20000 30000 40000 50000 60000 70000 80000 90000 100000 110000 120000 130000 140000 150000 160000 170000 180000 190000 200000 210000 220000 230000 240000 250000 260000 270000 280000 290000 300000 310000 320000 330000 340000 350000 360000 370000 380000 390000 400000 410000 420000 430000 440000 450000 460000 470000 10000 "
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "train=pd.read_csv(r'D:\\Data\\IJCAI\\no_fixedday\\train_mispret.csv')\n",
    "test=pd.read_csv(r'D:\\Data\\IJCAI\\no_fixedday\\test_mispret.csv')\n",
    "j=0\n",
    "for data in combine:\n",
    "    for ind in data.index:\n",
    "        if len(data.loc[ind,'item_category_list'].split(';'))==2:\n",
    "            data.loc[ind,'item_category_list']=data.loc[ind,'item_category_list']+';1000'\n",
    "        if (ind%10000==0) and (ind!=0):\n",
    "            print(ind,end=' ')\n",
    "    #进行分列赋值操作\n",
    "    data['item_category_list_dup'], data['item_category_list_0'],data['item_category_list_1'] = zip(*data['item_category_list'].map(lambda x: x.split(';')))\n",
    "    data.drop(['item_category_list_dup'],axis=1,inplace=True)\n",
    "train.to_csv(r'D:\\Data\\IJCAI\\no_fixedday\\train_parse.csv',index=False)\n",
    "test.to_csv(r'D:\\Data\\IJCAI\\no_fixedday\\test_parse.csv',index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 将'predict_category_property'列分成属性和类目分别对待\n",
    "***\n",
    "- 对类目下的属性不是-1的并且train和test共有的类目填充增列\n",
    "- 属性需要满足在共有类目下、train和test共有、并且倒排表中属性所在的行数不超过10万行不低于1900行"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "train=pd.read_csv(r'D:\\Data\\IJCAI\\no_fixedday\\train_parse.csv')\n",
    "test=pd.read_csv(r'D:\\Data\\IJCAI\\no_fixedday\\test_parse.csv')\n",
    "h_=train['predict_category_property'].apply(lambda x: x.split(';'))\n",
    "h=test['predict_category_property'].apply(lambda x: x.split(';'))\n",
    "listrain=[]\n",
    "listrainpro=[]\n",
    "#对train和test分别提取类目，属性不为-1\n",
    "for i in h_:\n",
    "    for j in i:\n",
    "        if j.split(':')[1]!='-1':\n",
    "            listrain.append(j.split(':')[0])\n",
    "listest=[]\n",
    "listestpro=[]\n",
    "for i in h:\n",
    "    for j in i:\n",
    "        if j.split(':')[1]!='-1':\n",
    "            listest.append(j.split(':')[0])\n",
    "com=set(listrain)&set(listest)             #共有的类目\n",
    "print('len(com)',len(com))\n",
    "#对train和test分别提取属性，属性必须是共有类目下的属性\n",
    "for i in h_:\n",
    "    for j in i:\n",
    "        if (j.split(':')[1]!='-1') and (j.split(':')[0] in com):\n",
    "            listrainpro.extend(j.split(':')[1].split(','))\n",
    "\n",
    "for i in h:\n",
    "    for j in i:\n",
    "        if (j.split(':')[1]!='-1') and (j.split(':')[0] in com):\n",
    "            listestpro.extend(j.split(':')[1].split(','))\n",
    "######################################################################\n",
    "comm=set(listrainpro)&set(listestpro)     #共有的属性\n",
    "print('len(comm)',len(comm))\n",
    "#对属性建立倒排表，对每个属性，找出他出现的行值index\n",
    "dic={}                                                            \n",
    "for ind in train.index:\n",
    "    for j in train.loc[ind,'predict_category_property'].split(';'):\n",
    "        if (j.split(':')[1]!='-1') and (j.split(':')[0] in com):\n",
    "            for k in j.split(':')[1].split(','):\n",
    "                if k in comm:\n",
    "                    if int(k) not in dic.keys():\n",
    "                        dic[int(k)]=[]\n",
    "                        dic[int(k)].append(ind)\n",
    "                    else:\n",
    "                        dic[int(k)].append(ind)\n",
    "dicte={}\n",
    "for ind in test.index:\n",
    "    for j in test.loc[ind,'predict_category_property'].split(';'):\n",
    "        if (j.split(':')[1]!='-1') and (j.split(':')[0] in com):\n",
    "            for k in j.split(':')[1].split(','):\n",
    "                if k in comm:\n",
    "                    if int(k) not in dicte.keys():\n",
    "                        dicte[int(k)]=[]\n",
    "                        dicte[int(k)].append(ind)\n",
    "                    else:\n",
    "                        dicte[int(k)].append(ind)\n",
    "#对倒排表进行排序（可选的），对每个属性出现的行数进行计数，并设置阈值，找到合适的填充列数\n",
    "dick=sorted(dic.items(),key=lambda x:x[0])\n",
    "dictek=sorted(dicte.items(),key=lambda x:x[0])\n",
    "num_dic={}\n",
    "for i,k in dick:\n",
    "    num_dic[i]=len(k)\n",
    "new=[]\n",
    "for key,value in num_dic.items():\n",
    "    if (value<100000) and (value>1900):\n",
    "        new.append(key)\n",
    "print('需要填充的属性列数',len(new))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Done!\n",
      "10000 20000 30000 40000 50000 60000 70000 80000 90000 100000 "
     ]
    }
   ],
   "source": [
    "#对类目进行分列并填充\n",
    "combine=[train,test]\n",
    "######################################################################\n",
    "for data in combine:\n",
    "    for i in com:\n",
    "        data['predict_category_%s'%str(i)]=0\n",
    "combine=[train,test]\n",
    "print('Done!')\n",
    "######################################################################\n",
    "for data in combine:\n",
    "    for ind in data.index:\n",
    "        for j in data.loc[ind,'predict_category_property'].split(';'):\n",
    "            if (j.split(':')[1]!='-1') and (j.split(':')[0] in com):\n",
    "                data.loc[ind,'predict_category_%s'%str(j.split(':')[0])]=1\n",
    "        if (ind%10000==0) and (ind!=0):\n",
    "            print(ind,end=' ')\n",
    "print('cate has Done!')\n",
    "######################################################################"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#对属性进行分列并填充\n",
    "print('Waiting for train...')\n",
    "count=0\n",
    "for i in new:\n",
    "    train['predict_property_%s'%str(i)]=0\n",
    "for key,value in dic:\n",
    "    if key not in new:\n",
    "        continue\n",
    "    else:\n",
    "        train.loc[value,'predict_property_%s'% str(key)]=1\n",
    "        count+=1\n",
    "    if count%10==0:\n",
    "        print(count,end=' ')\n",
    "print('train has been done!')\n",
    "train.to_csv(r'D:\\Data\\IJCAI\\no_fixedday\\train_parseo.csv',index=False)\n",
    "\n",
    "count=0\n",
    "for i in new:\n",
    "    test['predict_property_%s'%str(i)]=0\n",
    "for key,value in dicte:\n",
    "    if key not in new:\n",
    "        continue\n",
    "    else:\n",
    "        test.loc[value,'predict_property_%s'% str(key)]=1\n",
    "        count+=1\n",
    "    if count%10==0:\n",
    "        print(count,end=' ')\n",
    "print('test has been done!')\n",
    "test.to_csv(r'D:\\Data\\IJCAI\\no_fixedday\\test_parseo.csv',index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
